# modules/excel_processor.py
import datetime
import os
import pandas as pd
from openpyxl import load_workbook
from openpyxl.styles import numbers, Alignment, Font, Border, Side, PatternFill
from openpyxl.utils import get_column_letter
from .address_splitter import split_address


def process_excel(file_path, add_log, config=None):
    """
    处理Excel文件的主要函数

    参数:
        file_path: 输入文件路径
        add_log: 日志记录函数
        configs: 包含样式配置的字典，包含以下可选键:
            - title_color: 标题背景颜色(HEX格式)
            - font_name: 字体名称
            - font_size: 字体大小
            - bold: 是否加粗
            - title_height: 标题行高
            - row_height: 内容行高
            - col_widths: 各列宽度字典，如{'A': 20, 'B': 30}
    """
    try:
        if config is None:
            config = {}

        df = pd.read_excel(file_path)
        if '地址' not in df.columns:
            add_log("未找到“地址”列", error=True)
            return

        # 地址拆分处理
        df[['姓名', '电话', '地址_新']] = df['地址'].apply(lambda x: pd.Series(split_address(x)))
        df['地址'] = df['地址_新']
        df.drop(columns=['地址_新'], inplace=True)

        # 日期处理
        today_str = datetime.datetime.now().strftime("%Y-%m-%d")
        if '日期' in df.columns:
            df['日期'] = pd.to_datetime(df['日期'], errors='coerce')
            if df['日期'].isnull().all():
                fill_date = pd.to_datetime(today_str)
                df.loc[1:40, '日期'] = fill_date
            cols = list(df.columns)
            cols.insert(0, cols.pop(cols.index('日期')))
            df = df[cols]
        else:
            add_log("未找到“日期”列", error=True)
            return

        # 生成保存路径
        base_name = os.path.basename(file_path)
        name_wo_ext = os.path.splitext(base_name)[0]
        save_name = f"{today_str}_{name_wo_ext}.xlsx"
        save_path = os.path.join(os.path.dirname(file_path), save_name)

        # 保存DataFrame到Excel
        df.to_excel(save_path, index=False)

        # 打开Excel文件进行样式设置
        wb = load_workbook(save_path)
        ws = wb.active

        # 获取配置参数，带默认值
        title_color = config.get('title_color', "#36648B")[1:]  # 去除 # 号，默认颜色为 36648B
        title_font_color = config.get('title_font_color', "#000000")[1:]  # 新增：标题字体颜色（去除 #）
        font_name = config.get('font_name', "仿宋")  # 默认字体
        font_size = config.get('font_size', 12)  # 默认字号
        bold = config.get('bold', False)  # 默认不加粗
        title_height = config.get('title_height', 25)  # 默认标题行高
        row_height = config.get('row_height', 20)  # 默认内容行高
        col_widths = config.get('col_widths', {})  # 列宽设置
        col_switches = config.get('col_switches', {})  # 新增：列开关状态

        # 单元格边框样式
        thin = Side(border_style="thin", color="000000")
        border = Border(left=thin, right=thin, top=thin, bottom=thin)

        # 设置标题行样式
        fill = PatternFill(fill_type="solid", fgColor=title_color)  # 此处不再需要
        title_font = Font(name=font_name, size=16, bold=True, color=title_font_color)  # 字体颜色

        for col in range(1, 10):
            ws.merge_cells(start_row=1, start_column=col, end_row=2, end_column=col)
            cell = ws.cell(row=1, column=col)
            cell.alignment = Alignment(horizontal="center", vertical="center")
            cell.font = title_font
            cell.fill = fill

        # 设置标题行高
        ws.row_dimensions[1].height = title_height
        ws.row_dimensions[2].height = title_height

        # 设置内容单元格样式
        for row in ws.iter_rows(min_row=3, max_row=40, min_col=1, max_col=9):
            for cell in row:
                cell.border = border
                cell.font = Font(name=font_name, size=font_size, bold=bold)
                cell.alignment = Alignment(horizontal="center", vertical="center")

        # 设置内容行高
        for r in range(3, 41):
            ws.row_dimensions[r].height = row_height

        # 设置日期格式
        for row in ws.iter_rows(min_row=2, min_col=1, max_col=1):
            for cell in row:
                cell.number_format = numbers.FORMAT_DATE_YYYYMMDD2

        # ====== 获取原始列顺序 ======
        original_cols = [get_column_letter(i) for i in range(1, 10)]  # A - I

        # ====== 获取列宽 ======
        col_widths = config.get('col_widths', {})

        # ====== 获取要保留的列 ======
        cols_to_keep = config.get('col_switches', {})
        cols_to_keep = [col for col, show in cols_to_keep.items() if show]

        # ====== 删除未选中列 ======
        for col in reversed(original_cols):
            if col not in cols_to_keep:
                col_idx = original_cols.index(col) + 1
                ws.delete_cols(col_idx)

        # ====== 构建新的列宽参数（列宽随列前移） ======
        new_col_widths = shift_col_widths_forward(cols_to_keep, col_widths)

        # ====== 设置新的列宽 ======
        for idx in range(1, len(new_col_widths) + 1):
            col_letter = get_column_letter(idx)
            width = new_col_widths.get(col_letter)
            if width is not None and width > 0:
                ws.column_dimensions[col_letter].width = width

        # 保存工作簿
        wb.save(save_path)

        add_log(f"处理完成，已保存为：{save_path}", file_path=save_path)
    except Exception as e:
        add_log(f"处理失败：{e}", error=True)


def delete_unselected_columns(ws, cols_to_keep):
    """
    根据 cols_to_keep 列表，删除未选中的列
    :param ws: openpyxl 工作表对象
    :param cols_to_keep: 需要保留的列字母列表，如 ['A', 'C', 'E']
    """
    all_columns = [get_column_letter(i) for i in range(1, 10)]  # A - I
    # 从后往前删，避免列号变化影响后续删除
    for col_letter in reversed(all_columns):
        if col_letter not in cols_to_keep:
            col_idx = all_columns.index(col_letter) + 1  # 转为列索引
            ws.delete_cols(col_idx)


def shift_col_widths_forward(col_letters, col_widths):
    """
    列宽随列前移
    :param col_letters: 原始列顺序 ['A', 'B', 'C', 'D']
    :param col_widths: 原始列宽 {'A': 20, 'B': 20, 'C': 30}
    :return: 新的列宽 {'A': 20, 'B': 30, 'C': 20}
    """
    new_col_widths = {}
    new_idx = 1
    for col in col_letters:
        if col in col_widths:
            new_col_widths[get_column_letter(new_idx)] = col_widths[col]
            new_idx += 1
    return new_col_widths

